﻿Imports System.Data
Imports System.Data.SQLite
Imports System.Text

Public Class DBContext

    Public Const ID_LENGTH = 16
    Private _Path As String
    Private _UUID As Byte()

    Public Sub New(DBPath As String)

        _Path = DBPath

    End Sub

    Private ReadOnly Property ConnexionString As String
        Get
            Return "Data Source = """ & _Path & """ ; Version = 3; ConnectionPooling=100"
        End Get
    End Property

    Function GetWatchFromID(WatchID As Byte()) As Watch
        Using cn As New SQLiteConnection(ConnexionString)
            cn.Open()
            Using cmd As New SQLiteCommand
                cmd.Connection = cn
                cmd.CommandText = "select * from watch where id=" & GetBlobStringValue(WatchID)
                Dim dr As SQLiteDataReader = cmd.ExecuteReader
                Try
                    If dr.HasRows Then
                        dr.Read()
                        Return New Watch(dr)
                    Else
                        Return Nothing
                    End If
                Catch ex As Exception
                Finally
                    dr.Close()
                End Try
                cn.Close()
            End Using
        End Using
        Return Nothing
    End Function

    Function GetBrandFromID(p1 As Byte()) As Brand
        Throw New NotImplementedException
    End Function

    Shared Function GetBlobStringValue(blob As Byte()) As String
        If blob Is Nothing Then
            Return ""
        Else
            Dim sb As New StringBuilder
            sb.Append("X'")
            For Each b In blob
                sb.Append(b.ToString("X2"))
            Next
            sb.Append("'")
            Return sb.ToString
        End If
    End Function

    Public Sub LoadBrands(BrandList As List(Of Brand))

    End Sub

    Public Sub LoadWatches(WatchList As List(Of WatchView))
        Using cn As New SQLiteConnection(ConnexionString)
            cn.Open()
            Using cmd As New SQLiteCommand("select id from watch", cn)

                Dim dr As SQLiteDataReader = cmd.ExecuteReader
                Try
                    If dr.HasRows Then
                        While dr.Read
                            Dim Id() As Byte

                            ReDim Id(ID_LENGTH - 1)
                            dr.GetBytes(0, 0, Id, 0, ID_LENGTH)
                            WatchList.Add(New WatchView(Me, Id))
                        End While
                    End If
                Catch ex As Exception
                    Throw ex
                Finally
                    dr.Close()
                End Try

            End Using
            cn.Close()
        End Using
    End Sub

    Function GetBrandID(BrandName As String) As Byte()
        Using cn As New SQLiteConnection(ConnexionString)
            cn.Open()
            Using cmd As New SQLiteCommand("select id from brand where name='" & BrandName & "'", cn)

                Dim dr As SQLiteDataReader = cmd.ExecuteReader
                Try
                    If dr.HasRows Then
                        dr.Read()
                        Dim Id(ID_LENGTH - 1) As Byte
                        dr.GetBytes(0, 0, Id, 0, ID_LENGTH)
                        Return Id
                    End If
                Catch ex As Exception
                    Throw ex
                Finally
                    dr.Close()
                End Try

            End Using
            cn.Close()
        End Using
        Return Nothing
    End Function

    Sub UpdateorInSertWatch(ref As String, Name As String, RefBrand As Byte())

        Using cn As New SQLiteConnection(ConnexionString)
            cn.Open()
            ref = ref.Replace("""", "").Replace("'", "''")
            Name = Name.Replace("""", "").Replace("'", "''")

            Using cmd As New SQLiteCommand("select id from watch where reference='" & ref & "'", cn)

                Dim dr As SQLiteDataReader = cmd.ExecuteReader
                Try
                    If dr.HasRows Then
                        dr.Read()
                        Dim Id(ID_LENGTH - 1) As Byte
                        dr.GetBytes(0, 0, Id, 0, ID_LENGTH)
                        dr.Close()

                        Debug.Print("Update " & ref)

                        Dim Sql As String = "update watch set reference='" & ref & "', name='" & Name & "' where id=" & GetBlobStringValue(Id)
                        cmd.CommandText = GetSQLStatement(Sql)
                        cmd.ExecuteNonQuery()
                    Else
                        dr.Close()
                        Dim Sql As String = "insert into watch(id,reference,name,refbrand) values(randomblob(" & ID_LENGTH & "),'" & ref & "','" & Name & "'," & GetBlobStringValue(RefBrand) & ")"
                        cmd.CommandText = "Begin Transaction;" & vbCrLf & _
                                         Sql & ";" & vbCrLf & _
                                         GetActionLogStatement(Sql) & ";" & vbCrLf & _
                                         " commit transaction;"
                        cmd.ExecuteNonQuery()
                    End If
                Catch ex As Exception
                    Throw ex
                End Try

            End Using
            cn.Close()
        End Using

    End Sub

    Private Function GetUUID() As String

        If My.Settings.UUID = "" Then
            My.Settings.UUID = Guid.NewGuid.ToString
            My.Settings.Save()
        End If

        Dim ID As Byte() = New Guid(My.Settings.UUID).ToByteArray
        Return GetBlobStringValue(ID)
    End Function

    Private Function GetActionLogStatement(Sql As String) As String

        Dim Seq As Long = New DateTime(Now.Ticks, DateTimeKind.Utc).Ticks
        Dim retString As String = "insert into ActionLog (Id,Sequence,Date,Action) values (" & GetUUID() & _
                                    "," & Seq & ", DateTime('NOW'),'" & Sql.Replace("'", "''") & "')"

        Return retString

    End Function

    Private Function GetSQLStatement(Sql As String) As String
        Dim Ret As String = "Begin Transaction;" & vbCrLf & _
                            Sql & ";" & vbCrLf & _
                            GetActionLogStatement(Sql) & ";" & vbCrLf & _
                            " commit transaction;"
        Return Ret
    End Function

End Class
